package com.pointlion.sys.mvc.admin.evaluate.paperbase;

import com.jfinal.aop.Before;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.activerecord.tx.Tx;
import com.pointlion.sys.mvc.common.model.PaperBase;
import com.pointlion.sys.mvc.common.model.PaperUser;
import com.pointlion.sys.plugin.shiro.ShiroKit;

import java.util.List;

public class PaperBaseService{
	public static final PaperBaseService me = new PaperBaseService();
	public static final String TABLE_NAME = PaperBase.tableName;
	
	/***
	 * ����������ѯ
	 */
	public PaperBase getById(String id){
		return PaperBase.dao.findById(id);
	}
	
	/***
	 * ��ȡ��ҳ
	 */
	public Page<Record> getPage(int pnum,int psize){
		String sql  = " from "+TABLE_NAME+" o LEFT JOIN act_hi_procinst p ON o.proc_ins_id=p.ID_  where o.userid='"+ShiroKit.getUserId()+"'  order by o.create_time desc";
		return Db.paginate(pnum, psize, " select * ", sql);
	}
	
	/***
	 * ɾ��
	 * @param ids
	 */
	@Before(Tx.class)
	public void deleteByIds(String ids){
    	String idarr[] = ids.split(",");
    	for(String id : idarr){
    		PaperBase o = me.getById(id);
    		o.delete();
    	}
	}
	//查询总数
	public int getNum() {
		int total = Db.queryInt("select COUNT(id) from paper_base");
		return total;
	}
	public int getPaperNum() {
		int total = Db.queryInt("select COUNT(id) from paper");
		return total;
	}

    public List<PaperUser> getSecItem() {
		String sql = " SELECT\n" +
				"\tu.name,\n" +
				"\tround(sum(u.total_value)/count(base_id),2) as value\n" +
				"FROM\n" +
				"\tpaper_user u\n" +
				"where parent_id <> '#root' " +
				"group by base_id ";
		return  PaperUser.dao.find(sql);
	}

	public List<PaperUser> getFirItem() {
		String sql = " SELECT\n" +
				"\tu.name,\n" +
				"\tround(a.total/count(a.parent_id),2) value\n" +
				"FROM\n" +
				"\tpaper_user u\n" +
				"left join (select parent_id,sum(total_value) total from paper_user u where parent_id <> '#root' group by parent_id ) a on a.parent_id = u.base_id\n" +
				"where u.parent_id = '#root' \n" +
				"group by a.parent_id ";
		return  PaperUser.dao.find(sql);
	}

	public List<PaperUser> getAllName() {
		String sql = " SELECT\n" +
				"\tu.name\n" +
				"FROM\n" +
				"\tpaper_base u\n" +
				"group by id ";
		return  PaperUser.dao.find(sql);
	}
}